'''
    2021-12-06    22:38    by    CYL
    地点：
    苏州 团结桥 魔方公寓
    文件名：    demo0.py
    用途：
    1.用于学习数据库的使用，以及复习python。
    2.由于是出于复习的目的，所以代码写得都很简单无脑，不会套太多东西。
'''
import os
import time
import sqlite3

class getInfo_SQLite3():# 这个括号里的参数指的是类继承。
    '''
    2021-12-06   by    CYL
    用途：
    1.用于复习python的类。
    2.用于获取一些基础信息。
    '''
    def __init__(self):
        self.nowWorkplacePath = 'NULL'
        self.nowTime = 'NULL'
        self.nowSqliteVer = 'NULL'
        
    def getNowWorkplacePath(self)->str:
        self.nowWorkplacePath = os.getcwd()
        print('now Workplace Path is ',self.nowWorkplacePath)
        return self.nowWorkplacePath

    def getNowTime(self)->str:
        self.nowTime = time.ctime(time.time())
        print('now time is ',self.nowTime)
        return self.nowTime

    def getNowSQLiteVersion(self)->str:
        self.nowSqliteVer = sqlite3.sqlite_version
        print('now SQLite Version is ',self.nowSqliteVer)
        return self.nowSqliteVer 


if __name__ == "__main__":
    demo0_OBJ = getInfo_SQLite3()
    demo0_OBJ.getNowTime()
    demo0_OBJ.getNowSQLiteVersion()
    demo0_OBJ.getNowWorkplacePath()
    
#------写入一行数据至.db文件
    connectionObject = sqlite3.connect('demo0_SQLite.db')   #   1.创建或连接一个Connect对象。（当该.db文件不存在时，会自动创建）
    cursorObject = connectionObject.cursor()    #   2.创建一个Cursor游标对象。

    #   使用Cursor中的execute()方法来执行SQL语句。
    
    #   3.Create table
    try :
        cursorObject.execute('''CREATE TABLE table_0
                                        (date text, trans text, symbol text, qty real, price real)
                                        ''')
    except sqlite3.OperationalError :
        print("table_0 已存在！")
        #  注意断言语句 assert的用法！

    #   4.Insert a row of data
    cursorObject.execute('''INSERT INTO table_0 VALUES
                                        ('2021-12-06','BUY','肉夹馍',100,35.14)
                                        ''')
    #   5.Save (commit) the changes
    connectionObject.commit()

    #   6.We can also close the connection if we are done with it.
    #   Just be sure any changes have been committed or they will be lost.
    connectionObject.close()
#------读取一行数据至.db文件，源自官方文档library.pdf的P457
    connectionObject = sqlite3.connect('demo0_SQLite.db')
    cursorObject = connectionObject.cursor()
    searchTarget = ('肉夹馍',)
    cursorObject.execute('SELECT * FROM table_0 WHERE symbol=?',searchTarget)
    print(cursorObject.fetchall())
    connectionObject.commit()
    connectionObject.close()
